package study.source.sunghun.service.dbviewer;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import study.source.sunghun.dao.dbviewer.DatabaseViewerDAO;

@Service
public class DatabaseViewerService {
	private static Logger logger = LoggerFactory.getLogger(DatabaseViewerService.class);
	
	@Autowired
	private DatabaseViewerDAO dbViewer;

	public Map<String, Object> excuteQuery(String query) throws Exception {
		String tempQuery = query.toUpperCase();
		Map<String, Object> result = new HashMap<String, Object>();
		
		try {
			if (tempQuery.startsWith("UPDATE")) {
				result.put("queryType", "update");
				result.put("result", dbViewer.excuteUpdateQuery(query));
				
			} else if (tempQuery.startsWith("DELETE")) {
				result.put("queryType", "delete");
				result.put("result", dbViewer.excuteDeleteQuery(query));
				
			} else if (tempQuery.startsWith("INSERT")) {
				result.put("queryType", "insert");
				result.put("result", dbViewer.excuteInsertQuery(query));
				
			} else {
				result.put("queryType", "select");
				List<Map<String, Object>> data = dbViewer.excuteSelectQuery(query);
				result.put("result", this.makeTable(data));
			}
		} catch (Exception e) {
			logger.error("excuteQuery error ", e);
			result.put("queryType", "select");
			result.put("result", e.getMessage());
		}
		
		return result;
	}
	
	private String makeTable(List<Map<String, Object>> data) {
		StringBuffer sb = new StringBuffer();
		
		if (data != null && data.size() > 0) {
			List<String> columnList = new ArrayList<String>();
			sb.append("<table border='1' width='100%'><tr>");
			Map<String, Object> map = data.get(0);
			Set<String> keys = map.keySet();
			for (String columnName : keys) {
				sb.append("<td>").append(columnName).append("</td>");
				columnList.add(columnName);
			}
			sb.append("</tr>");
			
			for (Map<String, Object> row : data) {
				sb.append("<tr>");
				for (String name : columnList) {
					sb.append("<td>").append(row.get(name)).append("</td>");
				}
				sb.append("</tr>");
			}
			
			sb.append("</table>");
			
		} else {
			sb.append("Data None");
		}
		
		return sb.toString();
	}
}
